from flask import Flask, render_template, request, send_file, flash
import pandas as pd
import json
import openpyxl
from datetime import datetime
from lxml import html
import time
import requests

app = Flask(__name__)

@app.route('/jiudian', methods=['GET', 'POST'])
def jiudian():
    if request.method == 'POST':
        text_input = request.form.get('text_input')
        if text_input:
            try:
                get_info(text_input)
            except Exception as e:
                return "文本复制错误"

    # 读取 Excel 文件内容
    df = pd.read_excel('酒店价格.xlsx')
    now = datetime.now()
    temp = f"{now.year}-{now.month}-{now.day}"
    filtered_df = df[df['监测时间'] == temp]
    data = filtered_df.to_dict(orient='records')

    return render_template('jiudian.html', data=data)
def get(url):
    headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/108.0.5359.125 Safari/537.36'
      , 'cookie':'GUID=09031154418775625175; nfes_isSupportWebP=1; UBT_VID=1704701561712.a8ba9Sj8m9jl; _RSG=YH4NHOeh.A5BBvXvvjhPN9; _RDG=283dfe1b53e5992e4705a54e6960605cec; _RGUID=52beae86-115c-4462-95d9-aede798792cb; AHeadUserInfo=VipGrade=0&VipGradeName=%C6%D5%CD%A8%BB%E1%D4%B1&UserName=&NoReadMessageCount=0; DUID=u=01B7E2D1806C1C9F9D4C80C227F4D733&v=0; IsNonUser=F; _PRO_sso_lat_assertion_=3b2a69054bcfac1de34a4c425e4548564ab8c77a175edd56a1fe123379425766ad8d883fbbb6783677adbd62f7b24cb15a35aa774ca83a45da1c2e71187659af4bece8f41101a1a1747c272d38dbc9c581b0da251aa32f060d6d5ece00c5a277cf57821458ddb4ad2f363eeb435ad379046c2b48ed29c2913de7235403e2c59e; _PRO_sso_lat_assertion_signature_=2324c4c1d2c6b46413439788ac98913b5e8ad9084f3fb267a534b457940e2fcdff35f2dcf4211f712aae8f578bbb497c722e2c3757ab6c9f780499141fdcbe39e66685ec3294d9f741f01cfce2f2b077bf5d4b0c39d8aca3504343e3657cea36c713302bcf0779780d17f0456275a9938f3f8965280bb4e5820991689b21fd55; locale=zh-CN; _bfaStatusPVSend=1; _ubtstatus=%7B%22vid%22%3A%221704701561712.a8ba9Sj8m9jl%22%2C%22sid%22%3A1%2C%22pvid%22%3A8%2C%22pid%22%3A10650022581%7D; _bfaStatus=success; ibulanguage=CN; ibulocale=zh_cn; cookiePricesDisplayed=CNY; MKT_CKID=1693359296525.fsiym.wy1x; login_type=0; login_uid=E72898F584F270155299363E63B016BC; Union=OUID=&AllianceID=5376&SID=167068&SourceID=&createtime=1707096976&Expires=1707701775595; MKT_OrderClick=ASID=5376167068&AID=5376&CSID=167068&OUID=&CT=1707096975599&CURL=https%3A%2F%2Fhotels.ctrip.com%2F%3Fsid%3D167068%26allianceid%3D5376%26qh_keywordid%3D3483233637%26qh_creative%3D3453155524%26qh_planid%3D1875088274%26qh_unitid%3D1069672110%26qh_device%3Dpc%26qhclickid%3D2322bc3710919970%26keywordid%3D3483233637&VAL={"pc_vid":"1704701561712.a8ba9Sj8m9jl"}; _RF1=61.140.244.193; intl_ht1=h4=32_103801662,17_1640488,17_469216,2_427795,1_67'}
    res = requests.get(url, headers=headers)
    element = html.etree.HTML(res.text)
    print(element.findtext('.//title'))
    title = element.findtext('.//title').split("预")
    return title[0]

def get_info(text):
    data = json.loads(text)
    info = json.loads(data['Response']['traceInfo'])
    id = info['hotelId']
    rq = info['checkIn']
    url = f'https://hotels.ctrip.com/hotels/detail/?hotelId={id}'
    try:
        name = get(url)
    except:
        name = id

    
    result = []
    for count, each in enumerate(data['Response']['baseRooms']):
        type = each['baseRoom']['roomName']
        for aa in each['saleRoom']:
            try:
                result.append({"名称": name, "日期": rq, "房型": type, "售价": int(aa['money']['price']), "划线价": int(aa['money']['priceDelete']),"状态": aa['base']['isFullRoom'],"备注": aa['facility'][0]['content']})
            except:
                result.append({"名称": name, "日期": rq, "房型": type, "售价": int(aa['money']['price']), "划线价": -1,
                               "状态": aa['base']['isFullRoom'], "备注": aa['facility'][0]['content']})
    price = []
    price_hx = []
    fx = []
    now = datetime.now()
    wb = openpyxl.load_workbook('酒店价格.xlsx')
    w1 = wb['Sheet1']
    w2 = wb['Sheet2']
    for each in result:
        for i in range(2, 99999):
            if w2[f'A{i}'].value is None:
                w2.cell(row=i, column=1, value=name)
                w2.cell(row=i, column=2, value=f"{now.year}-{now.month}-{now.day}")
                w2.cell(row=i, column=3, value=each['日期'])
                w2.cell(row=i, column=5, value=each['房型'])
                w2.cell(row=i, column=6, value=each['售价'])
                w2.cell(row=i, column=7, value=each['划线价'])
                w2.cell(row=i, column=8, value=each['备注'])
                w2.cell(row=i, column=9, value=each['状态'])
                break
        if each["房型"] not in fx:
            fx.append(each["房型"])
        if each['状态'] == False:
            price.append(each["售价"])
            if each["划线价"] != -1:
                price_hx.append(each["划线价"])
    price = list(set(price))
    price_hx = list(set(price_hx))
    temp = str(fx).replace("[", "").replace("]", "").replace("'", "")
    if len(price) == 0:
        temp2 = "所有房型均已售完"
    if len(price) == 1:
        temp2 = f"价格为{price[0]}元"
    if len(price) > 1:
        temp2 = f'价格在{min(price)}-{max(price)}元之间'
    if len(price_hx) == 0:
        temp3 = ""
    if len(price_hx) == 1:
        temp3 = f"划线价为{price_hx[0]}元"
    if len(price_hx) > 1:
        temp3 = f'划线价在{min(price_hx)}-{max(price_hx)}元之间'
    if temp3 == "":
        txt = f'有{temp}等房型，{temp2}{temp3}。'
    else:
        txt = f'有{temp}等房型，{temp2},{temp3}。'

    for i in range(2, 99999):
        if w1[f'A{i}'].value is None:
            w1.cell(row=i, column=1, value=str(name) + str(rq))
            w1.cell(row=i, column=2, value=rq)
            w1.cell(row=i, column=3, value=txt)
            w1.cell(row=i, column=5, value=f"{now.year}-{now.month}-{now.day}")
            try:
                w1.cell(row=i, column=4, value=min(price))
            except ValueError:
                pass
            break
    wb.save('酒店价格.xlsx')
    print(name, rq, txt)

@app.route('/download')
def download_file():
    return send_file('酒店价格.xlsx', as_attachment=True)

@app.route('/delete_data')
def delete_data():
    source_file = "酒店价格模板.xlsx"
    destination_file = "酒店价格.xlsx"

    # 读取Excel文件中的所有工作表
    with pd.ExcelFile(source_file) as xls:
        # 读取所有工作表的数据
        sheets = {sheet_name: xls.parse(sheet_name) for sheet_name in xls.sheet_names}

    # 创建一个新的ExcelWriter对象
    with pd.ExcelWriter(destination_file, engine='openpyxl') as writer:
        # 遍历每个工作表并将它们写入新的Excel文件
        for sheet_name, data in sheets.items():
            data.to_excel(writer, sheet_name=sheet_name, index=False)
    return '数据已清空'

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5002)